package com.lehuo.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.lehuo.bean.Repair;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class RepairUtil {
	private static Statement statement = null;

	public static String addRepair(String user_id, String type, String project,
			String username, String describe, String tel, String reser_time,
			String address, String reser_type, String time,
			Connection connection) {
		String addRepair_sql = "insert into repair(user_id,type,project,des,username,tel,reser_time,address,reser_type,time)values('"
				+ user_id
				+ "','"
				+ type
				+ "','"
				+ project
				+ "','"
				+ describe
				+ "','"
				+ username
				+ "','"
				+ tel
				+ "','"
				+ reser_time
				+ "','"
				+ address + "','" + reser_type + "','" + time + "');";
		JSONObject jsonObject = new JSONObject();
		connection = ConnUtil.getConnection();
		try {
			statement = (Statement) connection.createStatement();
			System.out.println("addRepair_sql:" + addRepair_sql);
			int count = statement.executeUpdate(addRepair_sql,
					Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = statement.getGeneratedKeys();
			int key = -1;
			if (rs.next()) {
				key = rs.getInt(1);
				System.out.println(":" + key);
			}
			if (count > 0) {
				jsonObject.put("result", "success");
				jsonObject.put("id", key);
			} else {
				jsonObject.put("result", "error");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return jsonObject.toString();
	}

	public static String getRepair(String user_id, Connection connection) {
		ResultSet rs = null;
		JSONArray jsonArray = null;
		String sql = "select * from repair where user_id=" + user_id
				+ " order by id desc";
		List<Repair> repairs = new ArrayList<Repair>();
		try {
			connection = ConnUtil.getConnection();
			statement = (Statement) connection.createStatement();
			System.out.println("sql:" + sql);
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				Repair repair = new Repair(rs.getInt("id") + "",
						rs.getString("user_id"), rs.getString("type"),
						rs.getString("project"), rs.getString("des"),
						rs.getString("username"), rs.getString("tel"),
						rs.getString("reser_time"), rs.getString("address"),
						rs.getString("reser_type"), rs.getString("time"));
				repairs.add(repair);
			}
			jsonArray = JSONArray.fromObject(repairs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return jsonArray.toString();
	}

	public static String updataRepair(String id, String type, String project,
			String username, String des, String tel, String reser_time,
			String address, String reser_type, String time,
			Connection connection) {
		PreparedStatement pstmt = null;
		JSONObject jsonObject = new JSONObject();
		String updataRepair_sql = "UPDATE repair SET type= '" + type
				+ "',project= '" + project + "',username= '" + username
				+ "' ,des='" + des + "',tel='" + tel + "',reser_time='"
				+ reser_time + "',address='" + address + "',reser_type='"
				+ reser_type + "',time='" + time + "' where id= " + id;
		try {
			connection = ConnUtil.getConnection();
			System.out.println("addRepair_sql:" + updataRepair_sql);
			pstmt = connection.prepareStatement(updataRepair_sql);
			int num = pstmt.executeUpdate();
			if (num > 0) {
				jsonObject.put("result", "success");
			} else {
				jsonObject.put("result", "error");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return jsonObject.toString();
	}

	public static String deleteRepair(String id, Connection connection) {
		PreparedStatement pstmt = null;
		JSONObject jsonObject = new JSONObject();
		String deleteRepair_sql = "delete from repair where id=" + id;
		try {
			connection = ConnUtil.getConnection();
			pstmt = connection.prepareStatement(deleteRepair_sql);
			int num = pstmt.executeUpdate();
			
			if (num > 0) {
				jsonObject.put("result", "success");
			} else {
				jsonObject.put("result", "error");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return jsonObject.toString();
	}
}
